using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text;

using RecruitmentSystem.Common;

namespace RecruitmentSystem.DataAccess
{
    public partial class UserAccessor
    {
        public bool AddEnterpriseUser(EnterpriseUser user)
        {
            const string SpName = "SpAddEnterpriseUser";

            try
            {
                object[] parameters = new object[]
                {
                    user.Username,
                    user.Password,
                    user.Question,
                    user.Answer,
                    user.Name,
                    user.Province,
                    user.District,
                    user.TelephoneNumber,
                    user.CellphoneNumber,
                    user.MailAddress,
                    user.Homepage,
                    user.PostCode,
                    user.Address,
                    user.Code,
                    user.Category,
                    user.EnterpriseType,
                    user.CreateTime,
                    user.Asset,
                    user.Size,
                    user.Description,
                    user.Contactor,
                    user.FaxNumber,
                    Convert.ToInt32(user.Status)
                };

                SqlHelper.ExecuteNonQuery(Constants.ConnectionString, SpName, parameters);
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in add EnterpriseUser", ex);
            }

            return true;
        }

        public bool UpdatePasswordForEnterpriseUser(string newPassword, string oldPassword, string userId)
        {
            const string SpName = "SpChangePasswordByEnterpriseUserId";

            try
            {
                object[] parameters = new object[]
                {
                    newPassword,
                    oldPassword,
                    userId
                };

                object o = SqlHelper.ExecuteScalar(Constants.ConnectionString,
                    SpName, parameters);

                if (o != null)
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in change password for EnterpriseUser",
                    ex);
            }

            return true;
        }

        public bool UpdateEnterpriseUser(EnterpriseUser user)
        {
            const string SpName = "SpUpdateEnterpriseUser";

            try
            {
                object[] parameters = new object[]
                {
                    user.Username,
                    user.Name,
                    user.Province,
                    user.District,
                    user.TelephoneNumber,
                    user.CellphoneNumber,
                    user.MailAddress,
                    user.Homepage,
                    user.PostCode,
                    user.Address,
                    user.Code,
                    user.Category,
                    user.EnterpriseType,
                    user.CreateTime,
                    user.Asset,
                    user.Size,
                    user.Description,
                    user.Contactor,
                    user.FaxNumber
                };

                SqlHelper.ExecuteNonQuery(Constants.ConnectionString, SpName, parameters);
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in update EnterpriseUser", ex);
            }

            return true;
        }

        public EnterpriseUser GetEnterpriseUserById(string id)
        {
            const string SpName = "SpGetEnterpriseUser";
            EnterpriseUser user = null;
            SqlDataReader reader = null;

            try
            {
                reader = SqlHelper.ExecuteReader(Constants.ConnectionString,
                    SpName, new object[] { id });

                while (reader.Read())
                {
                    user = new EnterpriseUser();
                    user.Id = reader.GetInt32(0);
                    user.Username = !reader.IsDBNull(1) ? reader.GetString(1) : null;
                    user.Password = !reader.IsDBNull(2) ? reader.GetString(2) : null;
                    user.Question = !reader.IsDBNull(3) ? reader.GetString(3) : null;
                    user.Answer = !reader.IsDBNull(4) ? reader.GetString(4) : null;
                    user.Name = !reader.IsDBNull(5) ? reader.GetString(5) : null;
                    user.Province = !reader.IsDBNull(6) ? reader.GetString(6) : null;
                    user.District = !reader.IsDBNull(7) ? reader.GetString(7) : null;
                    user.TelephoneNumber = !reader.IsDBNull(8) ? reader.GetString(8) : null;
                    user.CellphoneNumber = !reader.IsDBNull(9) ? reader.GetString(9) : null;
                    user.MailAddress = !reader.IsDBNull(10) ? reader.GetString(10) : null;
                    user.Homepage = !reader.IsDBNull(11) ? reader.GetString(11) : null;
                    user.PostCode = !reader.IsDBNull(12) ? reader.GetString(12) : null;
                    user.Address = !reader.IsDBNull(13) ? reader.GetString(13) : null;
                    user.Code = !reader.IsDBNull(14) ? reader.GetString(14) : null;
                    user.Category = !reader.IsDBNull(15) ? reader.GetString(15) : null;
                    user.EnterpriseType = !reader.IsDBNull(16) ? reader.GetString(16) : null;
                    user.CreateTime = reader.GetDateTime(17);
                    user.Asset = !reader.IsDBNull(18) ? reader.GetString(18) : null;
                    user.Size = !reader.IsDBNull(19) ? reader.GetString(19) : null;
                    user.Description = !reader.IsDBNull(20) ? reader.GetString(20) : null;
                    user.Contactor = !reader.IsDBNull(21) ? reader.GetString(21) : null;
                    user.FaxNumber = !reader.IsDBNull(22) ? reader.GetString(22) : null;
                    user.Status = (UserStatus)Convert.ToInt32(reader.GetString(23));
                    user.Type = (EnterpriseType)Convert.ToInt32(reader.GetString(24));
                    user.LogoUrl = !reader.IsDBNull(25) ? reader.GetString(25) : null;
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in get enterprise user", ex);
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }

            return user;
        }

        public EnterpriseUser GetEnterpriseUserById(int id)
        {
            const string SpName = "SpGetEnterpriseUserById";
            EnterpriseUser user = null;
            SqlDataReader reader = null;

            try
            {
                reader = SqlHelper.ExecuteReader(Constants.ConnectionString,
                    SpName, new object[] { id });

                while (reader.Read())
                {
                    user = new EnterpriseUser();
                    user.Id = reader.GetInt32(0);
                    user.Username = !reader.IsDBNull(1) ? reader.GetString(1) : null;
                    user.Password = !reader.IsDBNull(2) ? reader.GetString(2) : null;
                    user.Question = !reader.IsDBNull(3) ? reader.GetString(3) : null;
                    user.Answer = !reader.IsDBNull(4) ? reader.GetString(4) : null;
                    user.Name = !reader.IsDBNull(5) ? reader.GetString(5) : null;
                    user.Province = !reader.IsDBNull(6) ? reader.GetString(6) : null;
                    user.District = !reader.IsDBNull(7) ? reader.GetString(7) : null;
                    user.TelephoneNumber = !reader.IsDBNull(8) ? reader.GetString(8) : null;
                    user.CellphoneNumber = !reader.IsDBNull(9) ? reader.GetString(9) : null;
                    user.MailAddress = !reader.IsDBNull(10) ? reader.GetString(10) : null;
                    user.Homepage = !reader.IsDBNull(11) ? reader.GetString(11) : null;
                    user.PostCode = !reader.IsDBNull(12) ? reader.GetString(12) : null;
                    user.Address = !reader.IsDBNull(13) ? reader.GetString(13) : null;
                    user.Code = !reader.IsDBNull(14) ? reader.GetString(14) : null;
                    user.Category = !reader.IsDBNull(15) ? reader.GetString(15) : null;
                    user.EnterpriseType = !reader.IsDBNull(16) ? reader.GetString(16) : null;
                    user.CreateTime = reader.GetDateTime(17);
                    user.Asset = !reader.IsDBNull(18) ? reader.GetString(18) : null;
                    user.Size = !reader.IsDBNull(19) ? reader.GetString(19) : null;
                    user.Description = !reader.IsDBNull(20) ? reader.GetString(20) : null;
                    user.Contactor = !reader.IsDBNull(21) ? reader.GetString(21) : null;
                    user.FaxNumber = !reader.IsDBNull(22) ? reader.GetString(22) : null;
                    user.Status = (UserStatus)Convert.ToInt32(reader.GetString(23));
                    user.Type = (EnterpriseType)Convert.ToInt32(reader.GetString(24));
                    user.LogoUrl = !reader.IsDBNull(25) ? reader.GetString(25) : null;
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in get enterprise user", ex);
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }

            return user;
        }

        public bool CheckEnterpriseUser(string id, string password)
        {
            const string SpName = "SpCheckEnterpriseUserPassword";

            try
            {
                object[] parameters = new object[]
                {
                    id,
                    password
                };

                object o = SqlHelper.ExecuteScalar(Constants.ConnectionString,
                    SpName, parameters);

                if (o == null)
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in check password for EnterpriseUser",
                    ex);
            }

            return true;
        }

        public bool CheckEnterpriseUserId(string id)
        {
            const string SpName = "SpCheckEnterpriseUserId";

            try
            {
                object[] parameters = new object[]
                {
                    id
                };

                object o = SqlHelper.ExecuteScalar(Constants.ConnectionString,
                    SpName, parameters);

                if (o == null)
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in check id for EnterpriseUser",
                    ex);
            }

            return true;
        }

        public int GetEnterpriseUserId(string id)
        {
            int userId = 0;
            const string SpName = "SpCheckEnterpriseUserId";

            try
            {
                object[] parameters = new object[]
                {
                    id
                };

                object o = SqlHelper.ExecuteScalar(Constants.ConnectionString,
                    SpName, parameters);

                if (o != null)
                {
                    userId = Convert.ToInt32(o);
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in get id for EnterpriseUser",
                    ex);
            }

            return userId;
        }

        public bool DeleteEnterpriseUser(string id)
        {
            const string SpName = "SpDeleteEnterpriseUser";

            try
            {
                object[] parameters = new object[]
                {
                    id
                };

                SqlHelper.ExecuteNonQuery(Constants.ConnectionString,
                    SpName, parameters);
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in delete EnterpriseUser", ex);
            }

            return true;
        }

        public bool DeleteEnterpriseUser(int id)
        {
            const string SpName = "SpDeleteEnterpriseUserById";

            try
            {
                object[] parameters = new object[]
                {
                    id
                };

                SqlHelper.ExecuteNonQuery(Constants.ConnectionString,
                    SpName, parameters);
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in delete EnterpriseUser", ex);
            }

            return true;
        }

        public bool UpdateEnterpriseUserStatus(int id, UserStatus status)
        {
            const string SpName = "SpUpdateEnterpriseUserStatus";

            try
            {
                object[] parameters = new object[]
                {
                    id,
                    Convert.ToInt32(status)
                };

                SqlHelper.ExecuteNonQuery(Constants.ConnectionString,
                    SpName, parameters);
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in update EnterpriseUser status", ex);
            }

            return true;
        }

        public int GetEnterpriseUserStatus(string id)
        {
            int status = 0;
            const string SpName = "SpGetEnterpriseUserStatus";

            try
            {
                object[] parameters = new object[]
                {
                    id
                };

                object o = SqlHelper.ExecuteScalar(Constants.ConnectionString, SpName, parameters);

                if (o != null)
                {
                    status = Convert.ToInt32(o);
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in get EnterpriseUser status", ex);
            }

            return status;
        }

        public List<User> GetEnterpriseUsers(UserStatus status, int pagesize, int pagecurrent)
        {
            List<User> users = new List<User>();
            const string SpName = "SpGetEnterpriseUsers";
            SqlDataReader reader = null;

            try
            {
                reader = SqlHelper.ExecuteReader(Constants.ConnectionString,
                    SpName, new object[] { Convert.ToInt32(status), pagesize, pagecurrent });

                while (reader.Read())
                {
                    EnterpriseUser user = new EnterpriseUser();

                    user.Id = reader.GetInt32(0);
                    user.Username = !reader.IsDBNull(1) ? reader.GetString(1) : null;
                    user.Password = !reader.IsDBNull(2) ? reader.GetString(2) : null;
                    user.Question = !reader.IsDBNull(3) ? reader.GetString(3) : null;
                    user.Answer = !reader.IsDBNull(4) ? reader.GetString(4) : null;
                    user.Name = !reader.IsDBNull(5) ? reader.GetString(5) : null;
                    user.Province = !reader.IsDBNull(6) ? reader.GetString(6) : null;
                    user.District = !reader.IsDBNull(7) ? reader.GetString(7) : null;
                    user.TelephoneNumber = !reader.IsDBNull(8) ? reader.GetString(8) : null;
                    user.CellphoneNumber = !reader.IsDBNull(9) ? reader.GetString(9) : null;
                    user.MailAddress = !reader.IsDBNull(10) ? reader.GetString(10) : null;
                    user.Homepage = !reader.IsDBNull(11) ? reader.GetString(11) : null;
                    user.PostCode = !reader.IsDBNull(12) ? reader.GetString(12) : null;
                    user.Address = !reader.IsDBNull(13) ? reader.GetString(13) : null;
                    user.Code = !reader.IsDBNull(14) ? reader.GetString(14) : null;
                    user.Category = !reader.IsDBNull(15) ? reader.GetString(15) : null;
                    user.EnterpriseType = !reader.IsDBNull(16) ? reader.GetString(16) : null;
                    user.CreateTime = reader.GetDateTime(17);
                    user.Asset = !reader.IsDBNull(18) ? reader.GetString(18) : null;
                    user.Size = !reader.IsDBNull(19) ? reader.GetString(19) : null;
                    user.Description = !reader.IsDBNull(20) ? reader.GetString(20) : null;
                    user.Contactor = !reader.IsDBNull(21) ? reader.GetString(21) : null;
                    user.FaxNumber = !reader.IsDBNull(22) ? reader.GetString(22) : null;
                    user.Status = (UserStatus)Convert.ToInt32(reader.GetString(23));
                    user.Type = (EnterpriseType)Convert.ToInt32(reader.GetString(24));
                    user.LogoUrl = !reader.IsDBNull(25) ? reader.GetString(25) : null;

                    users.Add(user);
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in get enterprise user", ex);
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }

            return users;
        }

        public int GetEnterpriseUsersCount(UserStatus status)
        {
            int count = 0;
            const string SpName = "SpGetEnterpriseUsersCount";

            try
            {
                object o = SqlHelper.ExecuteScalar(Constants.ConnectionString,
                    SpName, new object[] { Convert.ToInt32(status) });

                if (o != null)
                {
                    count = Convert.ToInt32(o);
                }

            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in get enterprise user count", ex);
            }

            return count;
        }

        public bool UpdateEnterpriseType(int id, int type)
        {
            const string SpName = "SpUpdateEnterpriseType";

            try
            {
                object[] parameters = new object[]
                {
                    id,
                    type
                };

                SqlHelper.ExecuteNonQuery(Constants.ConnectionString,
                    SpName, parameters);
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in update EnterpriseUser type", ex);
            }

            return true;
        }

        public bool UpdateEnterpriseLogo(string id, string logoUrl)
        {
            const string SpName = "SpUpdateEnterpriseLogo";

            try
            {
                object[] parameters = new object[]
                {
                    id,
                    logoUrl
                };

                SqlHelper.ExecuteNonQuery(Constants.ConnectionString,
                    SpName, parameters);
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in update EnterpriseUser Logo", ex);
            }

            return true;
        }

        public List<EnterpriseUser> GetHotEnterprise(int count)
        {
            const string SpName = "SpGetHotEnterprise";
            List<EnterpriseUser> users = new List<EnterpriseUser>();
            SqlDataReader reader = null;

            try
            {
                reader = SqlHelper.ExecuteReader(Constants.ConnectionString,
                    SpName, new object[] { count });

                while (reader.Read())
                {
                    EnterpriseUser user = new EnterpriseUser();

                    user.Id = reader.GetInt32(0);
                    user.Name = !reader.IsDBNull(1) ? reader.GetString(1) : null;

                    users.Add(user);
                }

                JobAccessor jobAccessor = new JobAccessor();

                foreach (EnterpriseUser user in users)
                {
                    user.Jobs = jobAccessor.GetPositionsByEnterpriseUserId(user.Id, PositionStatus.Activity);
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in get hot enterprise user", ex);
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }

            return users;
        }

        public List<EnterpriseUser> GetFamousEnterprise(int count)
        {
            List<EnterpriseUser> users = new List<EnterpriseUser>();
            const string SpName = "SpGetFamousEnterprise";
            SqlDataReader reader = null;

            try
            {
                reader = SqlHelper.ExecuteReader(Constants.ConnectionString,
                    SpName, new object[] { count });

                while (reader.Read())
                {
                    EnterpriseUser user = new EnterpriseUser();

                    user.Id = reader.GetInt32(0);
                    user.Name = !reader.IsDBNull(1) ? reader.GetString(1) : null;
                    user.LogoUrl = !reader.IsDBNull(2) ? reader.GetString(2) : null;

                    users.Add(user);
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in get famouts enterprise user", ex);
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }

            return users;
        }

        public List<EnterpriseUser> GetGenernalEnterprise(int count)
        {
            List<EnterpriseUser> users = new List<EnterpriseUser>();
            const string SpName = "SpGetNewPublishEnterprise";
            SqlDataReader reader = null;

            try
            {
                reader = SqlHelper.ExecuteReader(Constants.ConnectionString,
                    SpName, new object[] { count });

                while (reader.Read())
                {
                    EnterpriseUser user = new EnterpriseUser();

                    user.Id = reader.GetInt32(0);
                    user.Name = !reader.IsDBNull(1) ? reader.GetString(1) : null;
                    user.RecentlyPublishTime = !reader.IsDBNull(2) ? reader.GetDateTime(2) : (DateTime?)null;

                    users.Add(user);
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Exception occurs in get genernal enterprise user", ex);
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }

            return users;
        }
    }
}
